﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;

namespace WindowsFormsApplication1
{
    class HocSinh
    {
        public string connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source='";

        private string directory;
        public string Directory
        {
            get { return directory; }
            set { directory = value; }
        }



        private String hodem;
        public String Hodem
        {
            get { return hodem; }
            set { hodem = value; }
        }
        private String ten;

        public String Ten
        {
            get { return ten; }
            set { ten = value; }
        }
        private String ngaysinh;

        public String Ngaysinh
        {
            get { return ngaysinh; }
            set { ngaysinh = value; }
        }
        private String monthi;

        public String Monthi
        {
            get { return monthi; }
            set { monthi = value; }
        }
        private String lop;

        public String Lop
        {
            get { return lop; }
            set { lop = value; }
        }
        private String khoi;

        public String Khoi
        {
            get { return khoi; }
            set { khoi = value; }
        }
        private String truong;

        public String Truong
        {
            get { return truong; }
            set { truong = value; }
        }
        private String gvday;

        public String Gvday
        {
            get { return gvday; }
            set { gvday = value; }
        }
        private String gvboiduong;

        public String Gvboiduong
        {
            get { return gvboiduong; }
            set { gvboiduong = value; }
        }

        public String sobaodanh;
        public String Sobaodanh
        {
            get { return sobaodanh; }
            set { sobaodanh = value; }
        }
        public String diem;
        public String Diem
        {
            get { return diem; }
            set { diem = value; }
        }
        public String giai;
        public String Giai
        {
            get { return giai; }
            set { giai = value; }
        }

        public String ghichu;
        public String Ghichu
        {
            get { return ghichu; }
            set { ghichu = value; }
        }
        public String phongthi;
        public String Phongthi
        {
            get { return phongthi; }
            set { phongthi = value; }
        }

        public HocSinh()
        { }
        public HocSinh(String hodem, String ten, String ngaysinh, String monthi, String khoi
            , String lop, String truong, String gvday, String gvboiduong, String sobaodanh,
            String diem, String giai, String ghichu,String phongthi)
        {
            this.hodem = hodem;
            this.ten = ten;
            this.ngaysinh = ngaysinh;
            this.monthi = monthi;
            this.khoi = khoi;
            this.lop = lop;
            this.truong = truong;
            this.gvboiduong = gvboiduong;
            this.gvday = gvday;
            this.sobaodanh = sobaodanh;
            this.diem = diem;
            this.giai = giai;
            this.ghichu = ghichu;
            this.phongthi = phongthi;
        }
        public DataTable ListHocSinh()
        {
            HocSinh hs = new HocSinh();
            OleDbConnection conn = new OleDbConnection(connstr + directory + "';");
            conn.Open();
            string query = "SELECT * FROM [HocSinh$] WHERE ID <> NULL";
            OleDbCommand cmd = new OleDbCommand(query, conn);
            OleDbDataAdapter da = new OleDbDataAdapter();
            da.SelectCommand = cmd;
            DataTable dt = new DataTable();
            da.Fill(dt);
            conn.Close();
            return dt;
        }
        public void AddStudent(HocSinh hs)
        {
            String str = getStudentId();
            if (str == "" || str == null)
                str = "0";
            int id = Int32.Parse(str) + 1;
            OleDbConnection conn = new OleDbConnection(connstr + directory + "';");
            conn.Open();
            String query = "INSERT INTO [HocSinh$] VALUES('" + validId(id) + "',@hodem,@ten,@ngaysinh,@monthi,@khoi,@lop,@truong,@gvday,@gvboiduong,@sobaodanh,@diem,@giai,@ghichu,@phongthi)";

            try
            {
                OleDbCommand cmd = new OleDbCommand(query, conn);
                cmd.Parameters.AddWithValue("@hodem", SqlDbType.VarChar);
                cmd.Parameters.AddWithValue("@ten", SqlDbType.VarChar);
                cmd.Parameters.AddWithValue("@ngaysinh", SqlDbType.DateTime);
                cmd.Parameters.AddWithValue("@monthi", SqlDbType.VarChar);
                cmd.Parameters.AddWithValue("@khoi", SqlDbType.Real);
                cmd.Parameters.AddWithValue("@lop", SqlDbType.VarChar);
                cmd.Parameters.AddWithValue("@truong", SqlDbType.VarChar);
                cmd.Parameters.AddWithValue("@gvday", SqlDbType.VarChar);
                cmd.Parameters.AddWithValue("@gvboiduong", SqlDbType.VarChar);
                cmd.Parameters.AddWithValue("@sobaodanh", SqlDbType.VarChar);
                cmd.Parameters.AddWithValue("@diem", SqlDbType.Real);
                cmd.Parameters.AddWithValue("@giai", SqlDbType.VarChar);
                cmd.Parameters.AddWithValue("@ghichu", SqlDbType.VarChar);
                cmd.Parameters.AddWithValue("@phongthi", SqlDbType.VarChar);

                cmd.Parameters["@hodem"].Value = checkNull(hs.hodem);
                cmd.Parameters["@ten"].Value = checkNull(hs.ten);
                cmd.Parameters["@ngaysinh"].Value = DateTime.Parse(hs.ngaysinh);
                cmd.Parameters["@monthi"].Value = checkNull(hs.monthi);
                cmd.Parameters["@khoi"].Value = Double.Parse(hs.khoi);
                cmd.Parameters["@lop"].Value = checkNull(hs.lop);
                cmd.Parameters["@truong"].Value = checkNull(hs.truong);
                cmd.Parameters["@gvday"].Value = checkNull(hs.gvday);
                cmd.Parameters["@gvboiduong"].Value = checkNull(hs.gvboiduong);
                cmd.Parameters["@sobaodanh"].Value = "n/a";
                cmd.Parameters["@diem"].Value = "n/a";
                cmd.Parameters["@giai"].Value = "n/a";
                cmd.Parameters["@ghichu"].Value = "n/a";
                cmd.Parameters["@phongthi"].Value = "n/a";
                cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception e)
            {
                MessageBox.Show("Có lỗi khi thêm học sinh vào danh sách này.");
            }
            conn.Close();
        }

        public String getStudentId()
        {
            OleDbConnection conn = new OleDbConnection(connstr + directory + "';");
            conn.Open();
            string query = "SELECT MAX(ID) FROM [HocSinh$]";
            OleDbCommand cmd = new OleDbCommand(query, conn);
            try
            {
                string id = cmd.ExecuteScalar().ToString();
                conn.Close();
                return id;
            }
            catch (Exception ex)
            {
                conn.Close();
                return "0";
            }

        }

        public HocSinh getStudentById(string sid)
        {
            HocSinh hs = new HocSinh();
            OleDbConnection conn = new OleDbConnection(connstr + directory + "';");
            string sql = "SELECT * FROM [HocSinh$] WHERE ID ='" + sid + "'";
            conn.Open();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader dr = cmd.ExecuteReader();
            if (dr.Read())
            {
                hs.Hodem = dr.GetString(1);
                hs.Ten = dr.GetString(2);
                hs.Ngaysinh = dr.GetDateTime(3).ToString();
                hs.Monthi = dr.GetString(4);
                hs.Khoi = dr.GetDouble(5).ToString();
                hs.Lop = dr.GetString(6);
                hs.Truong = dr.GetString(7);
                hs.Gvday = dr.GetString(8);
                hs.Gvboiduong = dr.GetString(9);
            }
            dr.Close();
            conn.Close();
            return hs;
        }
        public void EditStudent(string sid, HocSinh hs)
        {
            OleDbConnection conn = new OleDbConnection(connstr + directory + "';");
            conn.Open();
            String query = "UPDATE [HocSinh$] SET [Họ Đệm] = @hodem,[Tên] = @ten,[Ngày sinh] = @ngaysinh,[Môn thi] = @monthi,[Khối] = @khoi,[Lớp] = @lop,[Giáo viên dạy] = @gvday,[Giáo viên bồi dưỡng] = @gvboiduong WHERE ID = '" + sid + "'";
            OleDbCommand cmd = new OleDbCommand(query, conn);
            cmd.Parameters.AddWithValue("@hodem", SqlDbType.VarChar);
            cmd.Parameters.AddWithValue("@ten", SqlDbType.VarChar);
            cmd.Parameters.AddWithValue("@ngaysinh", SqlDbType.DateTime);
            cmd.Parameters.AddWithValue("@monthi", SqlDbType.VarChar);
            cmd.Parameters.AddWithValue("@khoi", SqlDbType.VarChar);
            cmd.Parameters.AddWithValue("@lop", SqlDbType.VarChar);
            cmd.Parameters.AddWithValue("@gvday", SqlDbType.VarChar);
            cmd.Parameters.AddWithValue("@gvboiduong", SqlDbType.VarChar);

            cmd.Parameters["@hodem"].Value = checkNull(hs.hodem);
            cmd.Parameters["@ten"].Value = checkNull(hs.ten);
            cmd.Parameters["@ngaysinh"].Value = DateTime.Parse(hs.ngaysinh);
            cmd.Parameters["@monthi"].Value = checkNull(hs.monthi);
            cmd.Parameters["@khoi"].Value = Double.Parse(hs.khoi);
            cmd.Parameters["@lop"].Value = checkNull(hs.lop);
            cmd.Parameters["@gvday"].Value = checkNull(hs.gvday);
            cmd.Parameters["@gvboiduong"].Value = checkNull(hs.gvboiduong);
            cmd.ExecuteNonQuery();
            conn.Close();
        }
        public void DeleteStudent(string sid)
        {
            OleDbConnection conn = new OleDbConnection(connstr + directory + "';");
            conn.Open();
            String query = "UPDATE [HocSinh$] SET ID = '', [Họ Đệm] = '',[Tên] = '',[Ngày sinh] = null,[Môn thi] = '',[Khối] = null,[Lớp] = '',[Trường] = '',[Giáo viên dạy] = '',[Giáo viên bồi dưỡng] = '' WHERE ID = '" + sid + "'";
            OleDbCommand cmd = new OleDbCommand(query, conn);
            cmd.ExecuteNonQuery();
            conn.Close();
        }
        public DataTable ListHocSinhByName(String hodem, String ten, String monthi,
            String khoi, String lop, String gvday, String gvbd)
        {
            HocSinh hs = new HocSinh();
            OleDbConnection conn = new OleDbConnection(connstr + directory + "';");
            conn.Open();
            string query = "SELECT * FROM [HocSinh$] WHERE [Họ Đệm] LIKE '%" + hodem + "%' AND [Tên] LIKE '%" + ten + "%'AND [Môn thi] LiKE '%" + monthi
                + "%'AND [Khối] LiKE '%" + khoi + "%'AND [Lớp] LiKE '%" + lop + "%'AND [Giáo viên dạy] LiKE '%" + gvday + "%' AND [Giáo viên bồi dưỡng] LiKE '%" + gvboiduong + "'";
            OleDbCommand cmd = new OleDbCommand(query, conn);
            OleDbDataAdapter da = new OleDbDataAdapter();
            da.SelectCommand = cmd;
            DataTable dt = new DataTable();
            da.Fill(dt);
            conn.Close();
            return dt;
        }
        public String validId(int id)
        {
            int length = id.ToString().Length;
            string add0 = "";
            string ultiId = "";
            for (int i = 0; i < 6 - length; i++)
            {
                add0 = add0 + "0";
            }
            ultiId = add0 + id.ToString();
            return ultiId;
        }
        public String checkNull(string value)
        {
            if (value == null || value == "")
            {
                value = "n/a";
            }
            return value;
        }
    }
}
